package com.application.DataProvider;

import java.sql.Date;

import com.application.DTO.DetailLearningDTO;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DetailLearningDAO{

	Context _mcontext = null;

	public DetailLearningDAO(Context context){
		this._mcontext = context;
	}

	public int getIDMax()
	{
		int idMax = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select max(id) from DetailLearning";
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				idMax = Integer.valueOf(cur.getInt(0));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return idMax;
	}
	
	public boolean isExist(int id)
	{
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		boolean result = false;
		Cursor cur = null;
		try 
		{
			String sql = "Select count(*) from DetailLearning where ID=" +  id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				int i = Integer.valueOf(cur.getString(0));
				if(i != 0)
					result = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}
		return result;
	}
	
//	public String GetDetailLearningToDay(int idClass)
//	{
//		DataProvider provider = new DataProvider(_mcontext);
//		SQLiteDatabase db = provider.openDataBase();
//		Cursor cur = null;
//		String s = null;
//		try 
//		{
//			String sql = "select * from detaillearning where idclass = "+idClass+" and datediff(day, getdate(), datelearning)=0 and IDFeelType is not null order by idpart";
//			cur = provider.execQuery(db, sql);
//			while (cur.moveToNext()) {
//				if(s != null)
//					s+= ReadWriteFileController1.getSeparator1();
//				s += cur.getString(0) + ReadWriteFileController1.getSeparator()+ cur.getString(2) + ReadWriteFileController1.getSeparator() + 
//				cur.getString(1) + ReadWriteFileController1.getSeparator() + cur.getString(3) + ReadWriteFileController1.getSeparator() + 
//				cur.getString(4);
//			}
//		} catch (Exception e) {
//			// TODO: handle exception
//		} finally{
//			cur.close();
//			db.close();
//		}
//		return s;
//	}
	
	public DetailLearningDTO getDetailLearningID(int ID)
	{
		DetailLearningDTO dlDto = new DetailLearningDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from DetailLearning where ID = " + ID;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				dlDto.set_ID(cur.getInt(0));
				dlDto.set_IDClass(cur.getInt(1));
				dlDto.set_IDPart(cur.getInt(2));
				dlDto.set_DateLearning(Date.valueOf(cur.getString(3)));
				dlDto.set_IDFeelType(cur.getInt(4));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return dlDto;
	}

	public DetailLearningDTO getDetailLearningDate(Date date)
	{
		DetailLearningDTO dlDto = new DetailLearningDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from DetailLearning where DateLearning = " + date;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				dlDto.set_ID(cur.getInt(0));
				dlDto.set_IDClass(cur.getInt(1));
				dlDto.set_IDPart(cur.getInt(2));
				dlDto.set_DateLearning(Date.valueOf(cur.getString(3)));
				dlDto.set_IDFeelType(cur.getInt(4));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return dlDto;
	}
	public DetailLearningDTO getDetailLearningIDClass(int IDClass)
	{
		DetailLearningDTO dlDto = new DetailLearningDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from DetailLearning where IDClass = " + IDClass;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				dlDto.set_ID(cur.getInt(0));
				dlDto.set_IDClass(cur.getInt(1));
				dlDto.set_IDPart(cur.getInt(2));
				dlDto.set_DateLearning(Date.valueOf(cur.getString(3)));
				dlDto.set_IDFeelType(cur.getInt(4));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return dlDto;
	}
	
	public DetailLearningDTO getDetailLearningIDPart(int IDPart)
	{
		DetailLearningDTO dlDto = new DetailLearningDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from DetailLearning where IDPart = " + IDPart;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				dlDto.set_ID(cur.getInt(0));
				dlDto.set_IDClass(cur.getInt(1));
				dlDto.set_IDPart(cur.getInt(2));
				dlDto.set_DateLearning(Date.valueOf(cur.getString(3)));
				dlDto.set_IDFeelType(cur.getInt(4));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return dlDto;
	}
	public int getIDDetailLearning(int IDPart, int IDClass)
	{
		int id = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select ID from DetailLearning where IDPart = " + IDPart + " and IDClass = " + IDClass;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				id = cur.getInt(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return id;
	}
	
	public DetailLearningDTO getDetailLearningIDPart_Class(int IDPart, int IDClass)
	{
		DetailLearningDTO dlDto = new DetailLearningDTO();
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select * from DetailLearning where IDPart = " + IDPart + " and IDClass = " + IDClass;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				dlDto.set_ID(cur.getInt(0));
				dlDto.set_IDClass(cur.getInt(1));
				dlDto.set_IDPart(cur.getInt(2));
				dlDto.set_DateLearning(Date.valueOf(cur.getString(3)));
				dlDto.set_IDFeelType(cur.getInt(4));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return dlDto;
	}
	//Insert du lieu vao bang DetailLearning
	public int insertDetailLearning(DetailLearningDTO dlDto) {
		int result = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try {
			ContentValues values = new ContentValues();
			//int id = getIDMax();
			values.put("ID", dlDto.get_ID());
			values.put("IDClass", dlDto.get_IDClass());
			values.put("IDPart", dlDto.get_IDPart());
			if(dlDto.get_DateLearning() != null)
				values.put("DateLearning", dlDto.get_DateLearning().toString());
			else
				values.put("DateLearning", "");
			values.put("IDFeelType", dlDto.get_IDFeelType());
			values.put("Status", dlDto.is_Status());
			
			result = provider.execInsertCommand(db, "DetailLearning", values);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			db.close();
		}

		return result;
	}

	//Cap nhat bang DetailLearning
	public int updateDetailLearning(DetailLearningDTO dlDto)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{
			ContentValues values = new ContentValues();
			values.put("IDClass", dlDto.get_IDClass());
			values.put("IDPart", dlDto.get_IDPart());
			values.put("DateLearning", dlDto.get_DateLearning().toString());
			values.put("IDFeelType", dlDto.get_IDFeelType());
			values.put("Status", dlDto.is_Status());
			
			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(dlDto.get_ID())};
			result= provider.execUpdateCommand(db, "DetailLearning", values, whereClause, whereArgs);

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			db.close();
		}
		return result;
	}

	public int updateFeelType(int id, int idFeelType)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{
			ContentValues values = new ContentValues();
			values.put("IDFeelType", idFeelType);
			
			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(id)};
			result= provider.execUpdateCommand(db, "DetailLearning", values, whereClause, whereArgs);

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			db.close();
		}
		return result;
	}
	
	public int updateStatus(int id, boolean status)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{
			ContentValues values = new ContentValues();
			values.put("Status", status);
			
			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(id)};
			result= provider.execUpdateCommand(db, "DetailLearning", values, whereClause, whereArgs);

		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			db.close();
		}
		return result;
	}
	//Delete doi tuong DetailLearning
	public int deleteDetailLearning(int id)
	{
		int result=0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		try{	
			String whereClause="ID=?";
			String[]whereArgs = {String.valueOf(id)};	
			result=provider.execDeleteCommand(db, "DetailLearning", whereClause, whereArgs);
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			db.close();
		}
		return result;
	}

	public int getIDPart(int id)
	{
		int idPart = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select IDPart from DetailLearning where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				idPart = cur.getInt(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return idPart;

	}
	
	public int getIDFeelType(int id)
	{
		int idFeelType = 0;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select IDFeelType from DetailLearning where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				idFeelType = cur.getInt(0);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return idFeelType;

	}
	
	public boolean getStatus(int id)
	{
		boolean status = false;
		DataProvider provider = new DataProvider(_mcontext);
		SQLiteDatabase db = provider.openDataBase();
		Cursor cur = null;
		try 
		{
			String sql = "Select status from DetailLearning where ID = " + id;
			cur = provider.execQuery(db, sql);
			while (cur.moveToNext()) {
				if(cur.getInt(0) == 1)
				status = true;
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally{
			cur.close();
			db.close();
		}

		return status;
	}
}
